<?php
/**
 * Blog
 *
 * @copyright        2017 opencart.cn - All Rights Reserved
 * @link             http://www.guangdawangluo.com
 * @author           Sam Chen <sam.chen@opencart.cn>
 * @created          2017-05-19 09:53:11
 * @modified         2017-08-09 15:14:56
 */

class ModelBlogBlog extends Model {
  //////////////////////// INSTALLATION ////////////////////////
  public function install() {
    $file = DIR_APPLICATION . 'model/blog/install.sql';

    if (!file_exists($file)) {
      return;
    }

    $lines = file($file);

    if (!$lines) {
      return;
    }

    $sql = '';
    foreach ($lines as $line) {
      if ($line && (substr($line, 0, 2) != '--') && (substr($line, 0, 1) != '#')) {
        $sql .= $line;

        if (preg_match('/;\s*$/', $line)) {
          $sql = str_replace("DROP TABLE IF EXISTS `oc_", "DROP TABLE IF EXISTS `" . DB_PREFIX, $sql);
          $sql = str_replace("CREATE TABLE `oc_", "CREATE TABLE `" . DB_PREFIX, $sql);
          $sql = str_replace("INSERT INTO `oc_", "INSERT INTO `" . DB_PREFIX, $sql);

          $this->db->query($sql);

          $sql = '';
        }
      }
    }
  }

  //////////////////////// CATEGORY ////////////////////////
  public function saveCategory($category_id = 0, $data) {
    if ((int)$category_id) {
      $this->db->query("UPDATE " . DB_PREFIX . "blog_category SET status = '" . (int)$data['status'] . "', top ='" . (int)$data['top'] . "', sort_order = '" . (int)$data['sort_order'] . "', date_modified = NOW() WHERE category_id = '" . (int)$category_id . "'");
    } else {
      $this->db->query("INSERT INTO " . DB_PREFIX . "blog_category SET status = '" . (int)$data['status'] . "', top ='" . (int)$data['top'] . "', sort_order = '" . (int)$data['sort_order'] . "', date_added = NOW(), date_modified = NOW()");
      $category_id = $this->db->getLastId();
    }

    $this->db->query("DELETE FROM " . DB_PREFIX . "blog_category_description WHERE category_id = '" . (int)$category_id . "'");
    foreach ($data['category_description'] as $language_id => $value) {
      $this->db->query("INSERT INTO " . DB_PREFIX . "blog_category_description SET category_id = '" . (int)$category_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "', image='" . $this->db->escape($value['image']) . "', description = '" . $this->db->escape($value['description']) . "', meta_title = '" . $this->db->escape($value['meta_title']) . "', meta_description = '" . $this->db->escape($value['meta_description']) . "', meta_keyword = '" . $this->db->escape($value['meta_keyword']) . "'");
    }

    $this->db->query("DELETE FROM " . DB_PREFIX . "blog_category_to_store WHERE category_id = '" . (int)$category_id . "'");
    if (isset($data['category_store'])) {
      foreach ($data['category_store'] as $store_id) {
        $this->db->query("INSERT INTO " . DB_PREFIX . "blog_category_to_store SET category_id = '" . (int)$category_id . "', store_id = '" . (int)$store_id . "'");
      }
    }

    $this->db->query("DELETE FROM " . DB_PREFIX . "url_alias WHERE query = 'blog_category_id=" . (int)$category_id . "'");
    if (isset($data['keyword'])) {
      $this->db->query("INSERT INTO " . DB_PREFIX . "url_alias SET query = 'blog_category_id=" . (int)$category_id . "', keyword = '" . $this->db->escape($data['keyword']) . "'");
    }

    return $category_id;
  }

  public function getCategory($category_id) {
    $query = $this->db->query("SELECT DISTINCT *, (SELECT DISTINCT keyword FROM " . DB_PREFIX . "url_alias WHERE query = 'blog_category_id=" . (int)$category_id . "') AS keyword FROM " . DB_PREFIX . "blog_category bc LEFT JOIN " . DB_PREFIX . "blog_category_description bcd ON (bc.category_id = bcd.category_id) WHERE bc.category_id = '" . (int)$category_id . "' AND bcd.language_id = '" . (int)$this->config->get('config_language_id') . "'");

    return $query->row;
  }

  public function getCategoryDescriptions($category_id) {
    $category_description_data = array();

    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "blog_category_description WHERE category_id = '" . (int)$category_id . "'");

    foreach ($query->rows as $result) {
      $category_description_data[$result['language_id']] = array(
        'name'             => $result['name'],
        'image'            => $result['image'],
        'description'      => $result['description'],
        'meta_title'       => $result['meta_title'],
        'meta_description' => $result['meta_description'],
        'meta_keyword'     => $result['meta_keyword']
      );
    }

    return $category_description_data;
  }

  public function getCategoryStores($category_id) {
    $category_store_data = array();

    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "blog_category_to_store WHERE category_id = '" . (int)$category_id . "'");

    foreach ($query->rows as $result) {
      $category_store_data[] = $result['store_id'];
    }

    return $category_store_data;
  }

  public function getCategories($data = array()) {
    $sql = "SELECT * FROM " . DB_PREFIX . "blog_category bc LEFT JOIN " . DB_PREFIX . "blog_category_description bcd ON (bc.category_id = bcd.category_id) WHERE bcd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

    if (!empty($data['filter_name'])) {
      $sql .= " AND bcd.name LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
    }

    $sort_data = array(
      'bcd.name',
      'bc.sort_order'
    );

    if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
      $sql .= " ORDER BY " . $data['sort'];
    } else {
      $sql .= " ORDER BY bcd.name";
    }

    if (isset($data['order']) && ($data['order'] == 'DESC')) {
      $sql .= " DESC";
    } else {
      $sql .= " ASC";
    }

    if (isset($data['start']) || isset($data['limit'])) {
      if ($data['start'] < 0) {
        $data['start'] = 0;
      }

      if ($data['limit'] < 1) {
        $data['limit'] = 20;
      }

      $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
    }
    $query = $this->db->query($sql);

    return $query->rows;
  }

  public function getTotalCategories() {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "blog_category");

    return $query->row['total'];
  }

  public function deleteCategory($category_id) {
    $this->db->query("DELETE FROM " . DB_PREFIX . "blog_category WHERE category_id = '" . (int)$category_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "blog_category_description WHERE category_id = '" . (int)$category_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "blog_category_to_store WHERE category_id = '" . (int)$category_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "url_alias WHERE query = 'blog_category_id=" . (int)$category_id . "'");
  }

  //////////////////////// POST ////////////////////////
  public function savePost($post_id = 0, $data) {
    if ((int)$post_id) {
      $this->db->query("UPDATE " . DB_PREFIX . "blog_post SET status = '" . (int)$data['status'] . "', sort_order = '" . (int)$data['sort_order'] . "', date_added = '" . $this->db->escape($data['date_added']) . "', date_modified = NOW() WHERE post_id = '" . (int)$post_id . "'");
    } else {
      $this->db->query("INSERT INTO " . DB_PREFIX . "blog_post SET status = '" . (int)$data['status'] . "', sort_order = '" . (int)$data['sort_order'] . "', date_added = '" . $this->db->escape($data['date_added']) . "', date_modified = NOW()");

      $post_id = $this->db->getLastId();
    }
    $this->db->query("DELETE FROM " . DB_PREFIX . "blog_post_description WHERE post_id = '" . (int)$post_id . "'");

    foreach ($data['post_description'] as $language_id => $value) {
      $this->db->query("INSERT INTO " . DB_PREFIX . "blog_post_description SET post_id = '" . (int)$post_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "', image = '" . $this->db->escape($value['image']) . "', description = '" . $this->db->escape($value['description']) . "', content = '" . $this->db->escape($value['content']) . "', meta_title = '" . $this->db->escape($value['meta_title']) . "', meta_description = '" . $this->db->escape($value['meta_description']) . "', meta_keyword = '" . $this->db->escape($value['meta_keyword']) . "', author = '" . $value['author'] . "'");
    }

    $this->db->query("DELETE FROM " . DB_PREFIX . "blog_post_to_category WHERE post_id = '" . (int)$post_id . "'");
    if (isset($data['categories'])) {
      foreach ($data['categories'] as $category_id) {
        $this->db->query("INSERT INTO " . DB_PREFIX . "blog_post_to_category SET post_id = '" . (int)$post_id . "', category_id = '" . (int)$category_id . "'");
      }
    }

    $this->db->query("DELETE FROM " . DB_PREFIX . "url_alias WHERE query = 'blog_post_id=" . (int)$post_id . "'");

    if (isset($data['keyword'])) {
      $this->db->query("INSERT INTO " . DB_PREFIX . "url_alias SET query = 'blog_post_id=" . (int)$post_id . "', keyword = '" . $this->db->escape($data['keyword']) . "'");
    }

    return $post_id;
  }

  public function getPost($post_id) {
    $query = $this->db->query("SELECT DISTINCT *, (SELECT DISTINCT keyword FROM " . DB_PREFIX . "url_alias WHERE query = 'blog_post_id=" . (int)$post_id . "') AS keyword FROM " . DB_PREFIX . "blog_post bp LEFT JOIN " . DB_PREFIX . "blog_post_description bpd ON (bp.post_id = bpd.post_id) WHERE bp.post_id = '" . (int)$post_id . "' AND bpd.language_id = '" . (int)$this->config->get('config_language_id') . "'");

    return $query->row;
  }

  public function getPostDescriptions($post_id) {
    $post_description_data = array();

    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "blog_post_description WHERE post_id = '" . (int)$post_id . "'");
    foreach ($query->rows as $result) {
      $post_description_data[$result['language_id']] = array(
        'name'             => $result['name'],
        'image'            => $result['image'],
        'description'      => $result['description'],
        'content'          => $result['content'],
        'meta_title'       => $result['meta_title'],
        'meta_description' => $result['meta_description'],
        'meta_keyword'     => $result['meta_keyword'],
        'author'           => $result['author']
      );
    }

    return $post_description_data;
  }

  public function getPosts($data = array()) {
    $sql = "SELECT * FROM " . DB_PREFIX . "blog_post bp LEFT JOIN " . DB_PREFIX . "blog_post_description bpd ON (bp.post_id = bpd.post_id) WHERE bpd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

    if (!empty($data['filter_name'])) {
      $sql .= " AND bpd.name LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
    }

    $sort_data = array(
      'bpd.name',
      'bp.sort_order'
    );

    if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
      $sql .= " ORDER BY " . $data['sort'];
    } else {
      $sql .= " ORDER BY bpd.name";
    }

    if (isset($data['order']) && ($data['order'] == 'DESC')) {
      $sql .= " DESC";
    } else {
      $sql .= " ASC";
    }

    if (isset($data['start']) || isset($data['limit'])) {
      if ($data['start'] < 0) {
        $data['start'] = 0;
      }

      if ($data['limit'] < 1) {
        $data['limit'] = 20;
      }

      $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
    }
    $query = $this->db->query($sql);

    return $query->rows;
  }

  public function getTotalPosts() {
    $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "blog_post");

    return $query->row['total'];
  }

  public function getPostCategories($post_id) {
    $post_category_data = array();

    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "blog_post_to_category WHERE post_id = '" . (int)$post_id . "'");

    foreach ($query->rows as $result) {
      $post_category_data[] = $result['category_id'];
    }

    return $post_category_data;
  }

  public function deletePost($post_id) {
    $this->db->query("DELETE FROM " . DB_PREFIX . "blog_post WHERE post_id = '" . (int)$post_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "blog_post_description WHERE post_id = '" . (int)$post_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "blog_post_to_category WHERE post_id = '" . (int)$post_id . "'");
    $this->db->query("DELETE FROM " . DB_PREFIX . "url_alias WHERE query = 'blog_post_id=" . (int)$post_id . "'");
  }
}
